set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
with weight_difference_sum_day AS (
    select input_date,                                            --订单录入日期
           subordinate_agent_id,--string comment'所属代理区ID'
           max(subordinate_agent_code) as subordinate_agent_code, --所属代理区code
           max(subordinate_agent_name) as subordinate_agent_name, --所属代理区
           city_id,                                               --城市ID
           max(city_desc)              as city_desc,              -- 城市名字
           code,                                                  --网点code
           max(name)                   as name,                   --网点名字
           sum(if(settlement_weight+0.1 <first_weight,1,0))     as day_abnormal_sum,        --每日异常数据量
           sum(if(second_org_code is not null,1,0)) as active_abnormal_sum,  --有效异常数据
           sum(if(is_reweigh=1,1,0)) as reweigh_sum
    from jms_dm.dm_daily_waybill_weight_difference_detail_new_dt
    where dt>date_add('{{ execution_date | cst_ds }}', -20)
          and dt<=date_add('{{ execution_date | cst_ds }}', -6)
    group by input_date, subordinate_agent_id, city_id, code
    ),
     day_waybill_sum as (
         SELECT pick_network_code as input_network_code,                             --寄件网点编码
                dt,                                            --订单录入日期
                count(1)               as site_day_waybill_sum --网点每日运单总量
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt>date_add('{{ execution_date | cst_ds }}', -20)
               and dt<=date_add('{{ execution_date | cst_ds }}', -6)
         group by dt, pick_network_code
     )
 insert overwrite table jms_dm.dm_daily_waybill_weight_difference_sum_dt partition(dt)
select a.subordinate_agent_id,  --所属代理区ID
       a.subordinate_agent_code,--所属代理区coed
       a.subordinate_agent_name,--所属代理区名字
       a.city_id,               --所属城市ID
       a.city_desc,             --所属城市名字
       a.code,                  --所属网点code
       a.name,                  --所属网点名字
       a.day_abnormal_sum,      --每日网点异常运单量
       b.site_day_waybill_sum,  --每日网点总单量
       a.input_date,             --日期
       a.active_abnormal_sum,  --有效异常数据
       a.reweigh_sum,          --复称总票数
       a.input_date as dt
from weight_difference_sum_day a
         join day_waybill_sum b
              on a.input_date = b.dt
              and a.code = b.input_network_code
         distribute by 10;